﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SKG.DAL
{
    using Entities;
    using SKG.Extend;
    using System.Data;

    public class Ktx_SodienDAL:BaseDAL
    {

        #region Implement
        /// <summary>
        /// Count number of records
        /// </summary>
        /// <returns></returns>
        public int Count()
        {
            return _db.Sodiens.Count();
        }

        /// <summary>
        /// Return data by foreign key
        /// </summary>
        /// <param name="fKey">Foreign key on this table</param>
        /// <returns></returns>
        public DataTable Select(Guid fKey)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Select object of entity by secondary primary key (when need to)
        /// </summary>
        /// <param name="code">Primary key (when need to)</param>
        /// <returns></returns>
        public object Select(string code) 
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Return data
        /// </summary>
        /// <param name="obj">Object of entity</param>
        /// <param name="skip">Skip number of records</param>
        /// <param name="take">Take number of records</param>
        /// <returns></returns>
        public DataTable Select(object obj = null, int skip = 0, int take = 0)
        {
            try
            {
                var res = from s in _db.Sodiens join p in _db.Phongs on s.PhongId equals p.Id
                          orderby s.PhongId
                          select new
                          {
                              s.Id,
                              s.Thang,
                              s.Socu,
                              s.Somoi,
                              s.PhongId,
                              p.Maphong
                          };
                // return  Data.ToDataTable(res);
                return res.ToDataTable();

            }
            catch { return _tb; }
        }

        public DataTable Select(Guid code, DateTime dt)
        {
            try
            {
                var res = from s in _db.Sodiens
                          where s.PhongId==code 
                          && s.Thang.Month == dt.Month
                          && s.Thang.Year == dt.Year
                          orderby s.Thang
                          select new
                          {
                              s.Id,
                              s.Thang,
                              s.Socu,
                              s.Somoi,
                              s.PhongId,
                          };
                // return  Data.ToDataTable(res);
                return res.ToDataTable();

            }
            catch { return _tb; }
        }

        public DataTable Select_ID(Guid code) 
        {
            try
            {
                var res = from s in _db.Sodiens
                          where s.PhongId == code
                          orderby s.Thang descending
                          select new
                          {
                              s.Id,
                              s.Thang,
                              s.Socu,
                              s.Somoi,
                              s.PhongId,
                              s.Phong.Maphong
                          };
                // return  Data.ToDataTable(res);
                return res.ToDataTable();

            }
            catch { return _tb; }
        }

        /// <summary>
        /// Insert data
        /// </summary>
        /// <param name="obj">Object of entity</param>
        /// <returns></returns>
        public object Insert(object obj)
        {
            try
            {
                var o = (Ktx_Sodien)obj;
                if (Select(o.PhongId) == null) return null; // already exists
                o.Id = Guid.NewGuid();
                var oki = _db.Sodiens.Add(o);

                _db.SaveChanges();
                return oki;
            }
            catch { return null; }
        }

        /// <summary>
        /// Update data
        /// </summary>
        /// <param name="obj">Object of entity</param>
        /// <returns></returns>
        public object Update(object obj)
        {
            try
            {
                var o = (Ktx_Sodien)obj;
                var res = _db.Sodiens.SingleOrDefault(s => s.Id == o.Id);

                res.Thang = o.Thang;
                res.Socu = o.Socu;
                res.Somoi = o.Somoi;
                res.PhongId = o.PhongId;

                return _db.SaveChanges();
            }
            catch { return null; }
        }

        /// <summary>
        /// Delete data
        /// </summary>
        /// <param name="id">Primary key</param>
        /// <returns></returns>
        public object Delete(Guid code)
        {
            try
            {
                if (code != new Guid())
                {
                    var res = _db.Sodiens.SingleOrDefault(uuu => uuu.Id == code);
                    _db.Sodiens.Remove(res);
                }
                else
                {
                    var tmp = _db.Sodiens.ToList();
                    tmp.ForEach(s => _db.Sodiens.Remove(s));
                }
                return _db.SaveChanges();
            }
            catch { return null; }
        }
        #endregion

    }
}
